IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUserIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetUserIds]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************          
* Name:   [GetUserIds]          
* Purpose:  Retrieves matching user details filtered by admPersonIds.           
* Note: This stored procedure is a copy of the GetUsers excpet it returns only the admPersonIds(as the design caches only Ids).
* PARAMETERS(IN)          
* Name          Description               
* -------------      -------------------------------------------          
* @username       User Name to be matched       
* @lastName    last namme to be matched    
* @firstName      first name to be matched    
* @sortColumn     Represents the sort column     
* @sortOrder   Represents the sort order to be performed on the sort column  
*********************************************************************/       
      
CREATE Procedure [dbo].[GetUserIds](      
	 @username VARCHAR(36) = NULL,      
	 @lastName VARCHAR(64) = NULL,      
	 @firstName VARCHAR(64) = NULL,    
	 @personTypeId varchar(50) = NULL,
	 @sortColumn  varchar(200)='LastName',   
	 @sortOrder bit=1-->Ascending 0-->Descending  
	 
 )  
AS BEGIN  
       
SET NOCOUNT ON            
            
  if(@firstName is null)            
   Set @firstName='%'            
  else            
   Set @firstName=@firstName+'%'             
              
  if(@lastName is null)            
   Set @lastName='%'            
  else            
   Set @lastName=@lastName+'%'             
              
  if(@userName is null)            
   Set @userName='%'            
  else            
   Set @userName=@userName+'%'   

	IF (@personTypeId = 'True') 
     BEGIN
	  SELECT adp.Id AS [Id]  
		FROM AdmPerson adp      
		left join FwkDomainUser fdu      
		 ON adp.FwkDomainUserId = fdu.Id      
		Left Outer Join AdmInternalPerson aip      
		 ON adp.Id = aip.AdmPersonId       
		Left Outer JOIN FwkDomainOrganization org      
		 ON org.Id = aip.PrimaryOrganizationId   
		WHERE   ISNULL(adp.[firstName], '*') like @firstName            
				AND ISNULL(adp.[lastName], '*') like @lastName            
				AND  ISNULL(fdu.[userName], '*') like @userName								   
			  ORDER BY      
				CASE @sortOrder    
				  WHEN 0 THEN     
				   CASE @sortColumn    
					WHEN 'LastName' THEN adp.[LastName]   
					WHEN 'FirstName' THEN adp.[FirstName]   
					WHEN 'UserName' THEN fdu.[UserName]  
					WHEN 'Institution' THEN org.[FullyQualifiedName]                                            
					ELSE adp.[LastName]   
					END    
				END                 
				DESC,    
				CASE @sortOrder    
				  WHEN 1 THEN     
				   CASE @sortColumn    
					WHEN 'LastName' THEN adp.[LastName]   
					WHEN 'FirstName' THEN adp.[FirstName]   
					WHEN 'UserName' THEN fdu.[UserName]  
					WHEN 'Institution' THEN org.[FullyQualifiedName]                                            
					ELSE adp.[LastName]                      
				   END    
				END      
	END
ELSE
	BEGIN
	  SELECT adp.Id AS [Id]  
		FROM AdmPerson adp      
		left join FwkDomainUser fdu      
		 ON adp.FwkDomainUserId = fdu.Id      
		Left Outer Join AdmInternalPerson aip      
		 ON adp.Id = aip.AdmPersonId       
		Left Outer JOIN FwkDomainOrganization org      
		 ON org.Id = aip.PrimaryOrganizationId   
		WHERE   ISNULL(adp.[firstName], '*') like @firstName            
				AND ISNULL(adp.[lastName], '*') like @lastName            
				AND  ISNULL(fdu.[userName], '*') like @userName		
				and admPersonTypeId = 1          
			  ORDER BY      
				CASE @sortOrder    
				  WHEN 0 THEN     
				   CASE @sortColumn    
					WHEN 'LastName' THEN adp.[LastName]   
					WHEN 'FirstName' THEN adp.[FirstName]   
					WHEN 'UserName' THEN fdu.[UserName]  
					WHEN 'Institution' THEN org.[FullyQualifiedName]                                            
					ELSE adp.[LastName]   
					END    
				END                 
				DESC,    
				CASE @sortOrder    
				  WHEN 1 THEN     
				   CASE @sortColumn    
					WHEN 'LastName' THEN adp.[LastName]   
					WHEN 'FirstName' THEN adp.[FirstName]   
					WHEN 'UserName' THEN fdu.[UserName]  
					WHEN 'Institution' THEN org.[FullyQualifiedName]                                            
					ELSE adp.[LastName]                      
				   END    
				END      
	END       
END 